/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tamnhat.entity;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
public class Item implements Serializable {

    private String id;
    private String name;
    private Category categoryName;
    private Publisher publisherName;
    private int viewCount;
    private int downloadCount;
    private String lastUpdated;
    private ContentTemplate mediaType;
    private String content_type;

    public Item() {
        
    }

    public ContentTemplate getMediaType() {
        return mediaType;
    }

    public void setMediaType(ContentTemplate mediaType) {
        this.mediaType = mediaType;
    }
    
    public String getContent_type() {
        return content_type;
    } 

    public void setContent_type(String content_type) {
        this.content_type = content_type;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Category getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(Category categoryName) {
        this.categoryName = categoryName;
    }

    public Publisher getPublisherName() {
        return publisherName;
    }

    public void setPublisherName(Publisher publisherName) {
        this.publisherName = publisherName;
    }

    public int getViewCount() {
        return viewCount;
    }

    public void setViewCount(int viewCount) {
        this.viewCount = viewCount;
    }

    public int getDownloadCount() {
        return downloadCount;
    }

    public void setDownloadCount(int downloadCount) {
        this.downloadCount = downloadCount;
    }

    public String getLastUpdated() {
        return lastUpdated;
    }

    public void setLastUpdated(String lastUpdated) {
        this.lastUpdated = lastUpdated;
    }

    public static List<Item> allItem(String condition) throws Exception {
        List<Item> list = new ArrayList<Item>();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String strSQL = "select a.item_id,a.name,b.name,c.name,a.view_count,a.download_count,to_char(a.last_updated,'dd-mm-yyyy hh24:mi:ss') as n,a.media_type,a.content_type"
                + " from md_items a, md_categories b , md_publishers c"
                + " where (a.category_id = b.category_id(+)) and (a.publisher_id = c.publisher_id(+))"
                + " " + condition+ ""
                + " order by a.name";
        try {
            cn = AppServer.getConnection();
            st = cn.createStatement();
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                Item i = new Item();
                i.setId(rs.getString(1));
                i.setName(rs.getString(2));
                Category c = new Category();
                c.setName(rs.getString(3));
                i.setCategoryName(c);
                Publisher p = new Publisher();
                p.setName(rs.getString(4));
                i.setPublisherName(p);
                i.setViewCount(Integer.parseInt(rs.getString(5)));
                i.setDownloadCount(Integer.parseInt(rs.getString(6)));
                i.setLastUpdated(rs.getString(7));
                ContentTemplate ct = new ContentTemplate();
                ct.setName(rs.getString(8));
                i.setMediaType(ct);
                i.setContent_type(rs.getString(9));
                list.add(i);
            }
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return list;
    }

    public boolean insert() throws Exception {
        Connection cn = null;
        PreparedStatement pst = null;
        try {
            cn = AppServer.getConnection();
            String id = Database.getSequenceValue(cn, "seq_md_item");
            setId(id);
            String strSQL = " insert into md_items(item_id,name,category_id,publisher_id,view_count,download_count,last_updated,media_type,content_type) "
                    + " values(?,"
                    + " ?, "
                    + " ?, "
                    + " ?, "
                    + " 0, "
                    + " 0, "
                    + " SYSDATE, "
                    + " ?,"
                    + " ?) ";
            pst = cn.prepareStatement(strSQL);

            pst.setInt(1, Integer.parseInt(getId()));
            pst.setString(2, getName());
            pst.setInt(3, Integer.parseInt(getCategoryName().getId()));
            pst.setInt(4, Integer.parseInt(getPublisherName().getId()));            
            pst.setString(5, getMediaType().getName());
            pst.setString(6, getContent_type());

            return pst.executeUpdate() > 0;
            } finally {
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
    }

    public boolean insertProperty(String name, String value, Connection cn) throws Exception {
        PreparedStatement pst = null;
        String strSQL = " insert into md_item_properties values(seq_md_properties.nextval,?,?,?) ";
        try {
            pst = cn.prepareStatement(strSQL);


            pst.setInt(1, Integer.parseInt(getId()));
            pst.setString(2, name);
            pst.setString(3, value);
            
            return pst.executeUpdate() > 0;
        } finally {
            Database.closeObject(pst);
        }
    }

    //kiem tra xem property nay da ton tai chua. Neu chua thi insert
    public boolean checkExistProperty(String name, String value, Connection cn) throws Exception {
        PreparedStatement pst = null;
        ResultSet rs = null;
        String strSQL = "select * from md_item_properties "
                + " where item_id = ? and property_name = ?";
        try {
            pst = cn.prepareStatement(strSQL);

            pst.setInt(1, Integer.parseInt(getId()));
            pst.setString(2, name);

            rs = pst.executeQuery();
            
            return rs.next();
        } finally {
            Database.closeObject(rs);
            Database.closeObject(pst);
        }
    }    
    public boolean updateProperty(String name, String value, Connection cn) throws Exception {
        if(!checkExistProperty(name, value, cn)){
            return insertProperty(name, value, cn);
        }
        
        PreparedStatement pst = null;
        String strSQL = " update md_item_properties"
                + " set property_value = ?"
                + " where item_id = ? and property_name = ?";
        try {
            pst = cn.prepareStatement(strSQL);

            pst.setString(1, value);
            pst.setInt(2, Integer.parseInt(getId()));
            pst.setString(3, name);

            return pst.executeUpdate() > 0;
        } finally {
            Database.closeObject(pst);
        }
    }    
    
    public boolean update() throws Exception {
        Connection cn = null;
        PreparedStatement pst = null;
        String strSQL = " update md_items"
                + " set name = ?,"
                + " category_id = ?,"
                + " publisher_id = ?,"
                + " last_updated = SYSDATE, "
                + " media_type = ?,"
                + " content_type = ?"
                + " where item_id = ? ";
        try {
            cn = AppServer.getConnection();
            pst = cn.prepareStatement(strSQL);

            pst.setString(1, getName());
            pst.setInt(2, Integer.parseInt(getCategoryName().getId()));
            pst.setInt(3, Integer.parseInt(getPublisherName().getId()));
            pst.setString(4, getMediaType().getName());
            pst.setString(5, getContent_type());
            pst.setInt(6, Integer.parseInt(getId()));

            return pst.executeUpdate() > 0;
        } finally {
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
    }

    public boolean remove() throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;

        if (id != null || !id.equalsIgnoreCase("")) {
            String strSQL = " delete from md_items where item_id = " + getId() + " ";
            try {
                cn = AppServer.getConnection();
                stmt = cn.prepareStatement(strSQL);
                return stmt.executeUpdate() > 0;
            } finally {
                Database.closeObject(stmt);
                Database.closeObject(cn);
            }
        }
        return false;
    }
}
